﻿
'*******************************************************************
' Program: Data Center Visio Rack Tool
' Author: Albert E Edlund
' Date:
'
' Purpose:
'
'
'*******************************************************************

Imports Microsoft.Practices.EnterpriseLibrary.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling

Imports Microsoft.Office.Interop.Visio
Imports System.Runtime.InteropServices


Imports System
Imports System.ComponentModel
Imports System.Collections
Imports System.Collections.Generic
Imports System.Collections.ObjectModel


Imports System.Data
Imports System.IO
Imports System.Xml

Imports visRackLocations.clsDC_RackData

Public Class clsAttrLocation

#Region " Properties and definitions "

    Public m_db As Database
    Public m_ds As DataSet
    Public m_dt As DataTable


    Public m_dc As dc_SubLocation
    Public m_oc As Collection(Of dc_SubLocation)

    Private strUserId As String

    Private _clsVisio As visRackLocations.clsVisioSupport

    Public m_deleteCommand As DbCommand
    Public m_insertCommand As DbCommand
    Public m_updateCommand As DbCommand

    Public Const readAll As String = "selectTblAttrLocation"

    ' who am I
    Private myEnum As DC_RackTables = DC_RackTables.tblAttrLocation


    '********* visio snippet vb.net vis0041vb ******************
    Private _drawingApplication As Microsoft.Office.Interop.Visio.Application
    Public Property drawingApplication() As Microsoft.Office.Interop.Visio.Application
        Get
            Return Me._drawingApplication
        End Get
        Set(ByVal value As Microsoft.Office.Interop.Visio.Application)
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("ApplicationValueIsNull", _
                    "drawingApplication is Null.")
            End If
            Me._drawingApplication = value
        End Set
    End Property

    '********* visio snippet vb.net vis0043vb ******************
    Private _drawingDocument As Microsoft.Office.Interop.Visio.Document
    Public Property drawingDocument() As Microsoft.Office.Interop.Visio.Document
        Get
            Return Me._drawingDocument
        End Get
        Set(ByVal value As Microsoft.Office.Interop.Visio.Document)
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("DocumentValueIsNull", _
                    "drawingDocument is Null.")
            End If
            Me._drawingDocument = value
        End Set
    End Property

    '********* visio snippet vb.net vis0045vb ******************
    Private _drawingWindow As Microsoft.Office.Interop.Visio.Window
    Public Property drawingWindow() As Microsoft.Office.Interop.Visio.Window
        Get
            Return Me._drawingWindow
        End Get
        Set(ByVal value As Microsoft.Office.Interop.Visio.Window)
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("WindowValueIsNull", _
                    "drawingWindow is Null.")
            End If
            Me._drawingWindow = value
        End Set
    End Property

    '********* visio snippet vb.net vis0044vb ******************
    Private _drawingPage As Microsoft.Office.Interop.Visio.Page
    Public Property drawingPage() As Microsoft.Office.Interop.Visio.Page
        Get
            Return Me._drawingPage
        End Get
        Set(ByVal value As Microsoft.Office.Interop.Visio.Page)
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("PageValueIsNull", _
                    "drawingPage is Null.")
            End If
            Me._drawingPage = value
        End Set
    End Property

    Public m_customstencilpath As String = My.Settings.path_custom_stencils
    Public Property _CustomStencilPath() As String
        Get
            Return Me.m_customstencilpath
        End Get
        Set(ByVal value As String)
            Me.m_customstencilpath = value
        End Set
    End Property



#End Region

#Region " Move/Transform Data "

    '
    ' create columns
    '
    Public Sub CreateTableColumns(ByRef tempTable As DataTable)

        Dim newDC As dc_SubLocation = New dc_SubLocation(strUserId)

        Try

            Dim colattrlocKey As New System.Data.DataColumn
            colattrlocKey.ColumnName = "attrlocKey"
            colattrlocKey.DataType = GetType(Integer)
            colattrlocKey.DefaultValue = m_dc.attrlocKey
            tempTable.Columns.Add(colattrlocKey)

            Dim colattrlocSiteKey As New System.Data.DataColumn
            colattrlocSiteKey.ColumnName = "attrlocSiteKey"
            colattrlocSiteKey.DataType = GetType(Integer)
            colattrlocSiteKey.DefaultValue = m_dc.attrlocSiteKey
            tempTable.Columns.Add(colattrlocSiteKey)

            Dim colattrlocName As New System.Data.DataColumn
            colattrlocName.ColumnName = "attrlocName"
            colattrlocName.DataType = GetType(String)
            colattrlocName.DefaultValue = m_dc.attrlocName
            tempTable.Columns.Add(colattrlocName)

            Dim colattrlocBuilding As New System.Data.DataColumn
            colattrlocBuilding.ColumnName = "attrlocBuilding"
            colattrlocBuilding.DataType = GetType(String)
            colattrlocBuilding.DefaultValue = m_dc.attrlocBuilding
            tempTable.Columns.Add(colattrlocBuilding)

            Dim colattrlocFloor As New System.Data.DataColumn
            colattrlocFloor.ColumnName = "attrlocFloor"
            colattrlocFloor.DataType = GetType(String)
            colattrlocFloor.DefaultValue = m_dc.attrlocFloor
            tempTable.Columns.Add(colattrlocFloor)

            Dim colattrlocRoom As New System.Data.DataColumn
            colattrlocRoom.ColumnName = "attrlocRoom"
            colattrlocRoom.DataType = GetType(String)
            colattrlocRoom.DefaultValue = m_dc.attrlocRoom
            tempTable.Columns.Add(colattrlocRoom)

            Dim colattrlocCloset As New System.Data.DataColumn
            colattrlocCloset.ColumnName = "attrlocCloset"
            colattrlocCloset.DataType = GetType(String)
            colattrlocCloset.DefaultValue = m_dc.attrlocCloset
            tempTable.Columns.Add(colattrlocCloset)

            Dim colattrlocIntLocation As New System.Data.DataColumn
            colattrlocIntLocation.ColumnName = "attrlocIntLocation"
            colattrlocIntLocation.DataType = GetType(String)
            colattrlocIntLocation.DefaultValue = m_dc.attrlocIntLocation
            tempTable.Columns.Add(colattrlocIntLocation)

            Dim colattrlocGridHeight As New System.Data.DataColumn
            colattrlocGridHeight.ColumnName = "attrlocGridHeight"
            colattrlocGridHeight.DataType = GetType(Int16)
            colattrlocGridHeight.DefaultValue = m_dc.attrlocGridHeight
            tempTable.Columns.Add(colattrlocGridHeight)

            Dim colattrlocGridWidth As New System.Data.DataColumn
            colattrlocGridWidth.ColumnName = "attrlocGridWidth"
            colattrlocGridWidth.DataType = GetType(Int16)
            colattrlocGridWidth.DefaultValue = m_dc.attrlocGridWidth
            tempTable.Columns.Add(colattrlocGridWidth)

            Dim colattrlocGridSize As New System.Data.DataColumn
            colattrlocGridSize.ColumnName = "attrlocGridSize"
            colattrlocGridSize.DataType = GetType(Int16)
            colattrlocGridSize.DefaultValue = m_dc.attrlocGridSize
            tempTable.Columns.Add(colattrlocGridSize)

            Dim colattrlocPhone As New System.Data.DataColumn
            colattrlocPhone.ColumnName = "attrlocPhone"
            colattrlocPhone.DataType = GetType(String)
            colattrlocPhone.DefaultValue = m_dc.attrlocPhone
            tempTable.Columns.Add(colattrlocPhone)

            Dim colattrlocComments As New System.Data.DataColumn
            colattrlocComments.ColumnName = "attrlocComments"
            colattrlocComments.DataType = GetType(String)
            colattrlocComments.DefaultValue = m_dc.attrlocComments
            tempTable.Columns.Add(colattrlocComments)

            Dim colattrlocLastUpdate As New System.Data.DataColumn
            colattrlocLastUpdate.ColumnName = "attrlocLastUpdate"
            colattrlocLastUpdate.DataType = GetType(Date)
            colattrlocLastUpdate.DefaultValue = m_dc.attrlocLastUpdate
            tempTable.Columns.Add(colattrlocLastUpdate)

            Dim colattrlocLastEdit As New System.Data.DataColumn
            colattrlocLastEdit.ColumnName = "attrlocLastEdit"
            colattrlocLastEdit.DataType = GetType(String)
            colattrlocLastEdit.DefaultValue = m_dc.attrlocLastEdit
            tempTable.Columns.Add(colattrlocLastEdit)

            newDC = Nothing

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        If newDC IsNot Nothing Then
            newDC = Nothing
        End If

    End Sub

    '
    ' use for writing records
    '
    Public Sub movePropertiesToDataRow _
            (ByRef tempRow As DataRow, _
             ByVal tempDC As dc_SubLocation)

        Try

            With tempDC
                tempRow.SetField("attrlocKey", .attrlocKey)
                tempRow.SetField("attrlocSiteKey", .attrlocSiteKey)
                tempRow.SetField("attrlocName", .attrlocName)
                tempRow.SetField("attrlocBuilding", .attrlocBuilding)
                tempRow.SetField("attrlocFloor", .attrlocFloor)
                tempRow.SetField("attrlocRoom", .attrlocRoom)
                tempRow.SetField("attrlocCloset", .attrlocCloset)
                tempRow.SetField("attrlocIntLoction", .attrlocIntLocation)
                tempRow.SetField("attrlocGridHeight", .attrlocGridHeight)
                tempRow.SetField("attrlocGridWidth", .attrlocGridWidth)
                tempRow.SetField("attrlocGridSize", .attrlocGridSize)
                tempRow.SetField("attrlocPhone", .attrlocPhone)
                tempRow.SetField("attrlocComments", .attrlocComments)
                tempRow.SetField("attrlocLastUpdate", .attrlocLastUpdate)
                tempRow.SetField("attrlocLastEdit", strUserId)
            End With



        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    ' for reading records
    '
    Public Sub moveDataRowToProperties _
        (ByVal tempRow As DataRow)

        Try

            With m_dc
                .initProperties()
                .attrlocKey = tempRow.Item("attrlocKey")
                .attrlocSiteKey = tempRow.Item("attrlocSiteKey")
                .attrlocName = tempRow.Item("attrlocName")
                .attrlocBuilding = tempRow.Item("attrlocBuilding")
                .attrlocFloor = tempRow.Item("attrlocFloor")
                .attrlocRoom = tempRow.Item("attrlocRoom")
                .attrlocCloset = tempRow.Item("attrlocCloset")
                .attrlocIntLocation = tempRow.Item("attrlocIntLocation")
                .attrlocGridHeight = tempRow.Item("attrlocGridHeight")
                .attrlocGridWidth = tempRow.Item("attrlocGridWidth")
                .attrlocGridSize = tempRow.Item("attrlocGridSize")
                .attrlocPhone = tempRow.Item("attrlocPhone")
                .attrlocComments = tempRow.Item("attrlocComments")
                .attrlocLastUpdate = tempRow.Item("attrlocLastUpdate")
                .attrlocLastEdit = tempRow.Item("attrlocLastEdit")
            End With


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    ' for reading records
    '
    Public Sub moveDataRowToProperties _
        (ByVal tempRow As DataRow, _
         ByRef tempDC As dc_SubLocation)

        Try

            With tempDC
                .initProperties()
                .attrlocKey = tempRow.Item("attrlocKey")
                .attrlocSiteKey = tempRow.Item("attrlocSiteKey")
                .attrlocName = tempRow.Item("attrlocName")
                .attrlocBuilding = tempRow.Item("attrlocBuilding")
                .attrlocFloor = tempRow.Item("attrlocFloor")
                .attrlocRoom = tempRow.Item("attrlocRoom")
                .attrlocCloset = tempRow.Item("attrlocCloset")
                .attrlocIntLocation = tempRow.Item("attrlocIntLocation")
                .attrlocGridHeight = tempRow.Item("attrlocGridHeight")
                .attrlocGridWidth = tempRow.Item("attrlocGridWidth")
                .attrlocGridSize = tempRow.Item("attrlocGridSize")
                .attrlocPhone = tempRow.Item("attrlocPhone")
                .attrlocComments = tempRow.Item("attrlocComments")
                .attrlocLastUpdate = tempRow.Item("attrlocLastUpdate")
                .attrlocLastEdit = tempRow.Item("attrlocLastEdit")
            End With


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    '
    '
    Public Sub moveDataTableToObservableCollection _
            (ByVal dtTemp As DataTable)

        Try

            If 0 < dtTemp.Rows.Count Then

                If m_oc IsNot Nothing Then
                    m_oc = Nothing
                    m_oc = New Collection(Of dc_SubLocation)
                Else
                    m_oc = New Collection(Of dc_SubLocation)
                End If

                Dim drTemp As DataRow
                Dim dcTemp As dc_SubLocation
                For Each drTemp In dtTemp.Rows
                    dcTemp = New dc_SubLocation
                    moveDataRowToProperties(drTemp, dcTemp)
                    m_oc.Add(dcTemp)
                Next

            End If

        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try



    End Sub

    '
    '
    '
    Public Function moveDataTableToXML() As XmlDocument

        Dim writer = New System.IO.StringWriter
        Dim m_dt_xml As Xml.XmlDocument = Nothing

        Try


            m_dt.WriteXml(writer, XmlWriteMode.WriteSchema, True)
            m_dt_xml = Nothing
            m_dt_xml = New Xml.XmlDocument
            m_dt_xml.LoadXml(writer.ToString)

        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally

            writer = Nothing

        End Try

        Return m_dt_xml


    End Function


#End Region

#Region " standard calls "

    '
    '
    '
    Public Sub initDataSetAndTable()

        Try

            ' make sure the root stuff is there
            If m_ds Is Nothing Then
                m_ds = New DataSet
            Else
                m_ds = Nothing
                m_ds = New DataSet
            End If
            m_ds.SchemaSerializationMode = SchemaSerializationMode.IncludeSchema

            If m_dt Is Nothing Then
                m_dt = New DataTable
            Else
                m_dt = Nothing
                m_dt = New DataTable
            End If

            If m_oc Is Nothing Then
                m_oc = New Collection(Of dc_SubLocation)
            Else
                m_oc = Nothing
                m_oc = New Collection(Of dc_SubLocation)
            End If

            If m_dc Is Nothing Then
                m_dc = New dc_SubLocation(strUserId)
            Else
                m_dc = Nothing
                m_dc = New dc_SubLocation(strUserId)
            End If


            CreateTableColumns(m_dt)
            m_dt.TableName = DC_RackTables.tblAttrLocation.ToString
            m_ds.Tables.Add(m_dt)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try



    End Sub

    '
    '
    '
    Private Sub buildUpdateAttrLocationCommand()

        Try
            m_updateCommand = m_db.GetStoredProcCommand("updateAttrLocation")

            Dim cmdActive As DbCommand = m_updateCommand
            m_db.AddInParameter(cmdActive, "attrlocKey", DbType.Int32, "attrlocKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocSiteKey", DbType.Int32, "attrlocSiteKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocName", DbType.String, "attrlocName", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocBuilding", DbType.String, "attrlocBuilding", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocFloor", DbType.String, "attrlocFloor", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocRoom", DbType.String, "attrlocRoom", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocCloset", DbType.String, "attrlocCloset", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocIntLocation", DbType.String, "attrlocIntLocation", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocGridHeight", DbType.Int16, "attrlocGridHeight", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocGridWidth", DbType.Int16, "attrlocGridWidth", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocGridSize", DbType.Int16, "attrlocGridSize", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocPhone", DbType.String, "attrlocPhone", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocComments", DbType.String, "attrlocComments", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocLastUpdate", DbType.Date, "attrlocLastUpdate", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocLastEdit", DbType.String, "attrlocLastEdit", DataRowVersion.Current)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Private Sub buildInsertAttrLocationCommand()

        Try
            m_insertCommand = m_db.GetStoredProcCommand("insertAttrLocation")

            Dim cmdActive As DbCommand = m_insertCommand
            m_db.AddInParameter(cmdActive, "attrlocSiteKey", DbType.Int32, "attrlocSiteKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocName", DbType.String, "attrlocName", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocBuilding", DbType.String, "attrlocBuilding", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocFloor", DbType.String, "attrlocFloor", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocRoom", DbType.String, "attrlocRoom", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocCloset", DbType.String, "attrlocCloset", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocIntLocation", DbType.String, "attrlocIntLocation", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocGridHeight", DbType.Int16, "attrlocGridHeight", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocGridWidth", DbType.Int16, "attrlocGridWidth", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocGridSize", DbType.Int16, "attrlocGridSize", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocPhone", DbType.String, "attrlocPhone", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocComments", DbType.String, "attrlocComments", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocLastUpdate", DbType.Date, "attrlocLastUpdate", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocLastEdit", DbType.String, "attrlocLastEdit", DataRowVersion.Current)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Private Sub buildDeleteAttrLocationCommand()

        Try
            m_deleteCommand = m_db.GetStoredProcCommand("deleteAttrLocation")

            Dim cmdActive As DbCommand = m_deleteCommand
            m_db.AddInParameter(cmdActive, "attrlocKey", DbType.Int32, "attrlocKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "attrlocSiteKey", DbType.Int32, "attrlocSiteKey", DataRowVersion.Current)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    ' create a record based on property values move it to the generic datatable
    ' then copy the table to the database
    '
    Public Function addAttrLocationDataSourceRecord _
            (ByVal tempDC As dc_SubLocation, _
             Optional ByVal blnSilent As Boolean = False) _
             As Integer

        Dim intRowsAffected As Integer = 0

        Try

            initDataSetAndTable()

            Dim m_dr As DataRow = m_ds.Tables(DC_RackTables.tblAttrLocation.ToString).NewRow
            movePropertiesToDataRow(m_dr, tempDC)
            m_ds.Tables(dc_racktables.tblLocation.tostring).Rows.Add(m_dr)
            intRowsAffected = dbUpdateDataSet()

            If blnSilent = False Then
                MsgBox("DataRowsUpated = " & intRowsAffected)
            End If

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        Return intRowsAffected

    End Function

    '
    '
    '
    Public Function dbUpdateDataSet() As Int16

        Dim rowsAffected As Int16 = 0

        Try

            rowsAffected = m_db.UpdateDataSet(m_ds, DC_RackTables.tblAttrLocation.ToString, _
                                        m_insertCommand, _
                                        m_updateCommand, _
                                        m_deleteCommand, _
                                        UpdateBehavior.Standard)
            'msgbox rowsAffected

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If

        End Try

        Return rowsAffected

    End Function

#End Region

#Region " database queries "

    '
    '
    '
    Public Sub dbSelectAllAttrLocation()


        Try



            initDataSetAndTable()

            ' Retrieve the initial data
            Dim cmd As DbCommand = m_db.GetStoredProcCommand(readAll)
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblAttrLocation.ToString)

            m_dt = m_ds.Tables(DC_RackTables.tblAttrLocation.ToString)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    '
    '
    Public Sub dbSelectAttrLocationByLocSiteKey(ByVal locKey As Int32)


        Try



            initDataSetAndTable()

            ' Retrieve the initial data
            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectAttrLocationByLocSiteKey")
            m_db.AddInParameter(cmd, "@attrLocSiteKey", DbType.Int32, locKey)
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblAttrLocation.ToString)

            m_dt = m_ds.Tables(DC_RackTables.tblAttrLocation.ToString)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    '
    '
    Public Sub dbSelectAttrLocationByAttrLocKey(ByVal locKey As Int32)


        Try



            initDataSetAndTable()

            ' Retrieve the initial data
            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectAttrLocationByAttrLocKey")
            m_db.AddInParameter(cmd, "@attrLocKey", DbType.Int32, locKey)
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblAttrLocation.ToString)

            m_dt = m_ds.Tables(DC_RackTables.tblAttrLocation.ToString)
            ' if we find the record move it to the properties
            If m_dt.Rows.Count = 1 Then
                Dim m_dr As DataRow = m_dt.Rows(0)
                moveDataRowToProperties(m_dr)
            End If


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Public Sub dbSelectAttrLocationByCompKey(ByVal compKey As Int32)


        Try

            initDataSetAndTable()

            ' Retrieve the initial data
            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectAttrLocationByCompKey")
            m_db.AddInParameter(cmd, "@compKey", DbType.Int32, compKey)
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblAttrLocation.ToString)

            m_dt = m_ds.Tables(DC_RackTables.tblAttrLocation.ToString)
            ' if we find the record move it to the properties
            If m_dt.Rows.Count = 1 Then
                Dim m_dr As DataRow = m_dt.Rows(0)
                moveDataRowToProperties(m_dr)
            End If


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    ' we use this one to chase a collection of components, usually for the link attributes analysis
    '
    Public Sub dbSelectAttrLocationByComponentCollection _
        (ByVal colCompKeys As Collection)

        Dim dtTemp As DataTable = New DataTable
        Dim dtTempIn As DataTable = New DataTable
        Dim drTemp As DataRow
        Dim intCompKey As Int32


        Try

            ' use the collection of portkeys to create a custom portkey table
            For intCt = 1 To colCompKeys.Count
                If dtTempIn Is Nothing Then
                    dtTempIn = New DataTable
                Else
                    dtTempIn = Nothing
                    dtTempIn = New DataTable
                End If
                intCompKey = colCompKeys.Item(intCt)
                dbSelectAttrLocationByCompKey(intCompKey)
                dtTempIn = m_dt.Copy
                If intCt = 1 Then
                    dtTemp = dtTempIn.Copy
                Else
                    For Each drTemp In dtTempIn.Rows
                        With drTemp
                            dtTemp.ImportRow(drTemp)
                        End With
                    Next
                End If
            Next intCt ' port key


            ' clean the data caches again
            initDataSetAndTable()

            dtTemp.TableName = DC_RackTables.tblAttrLocation.ToString
            m_dt = dtTemp.Copy
            If m_ds.Tables(myEnum.ToString) IsNot Nothing Then
                m_ds.Tables(myEnum.ToString).Clear()
                For Each drTemp In dtTemp.Rows
                    m_ds.Tables(myEnum.ToString).ImportRow(drTemp)
                Next
            Else
                m_ds.Tables.Add(dtTemp.Copy)
            End If



        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub




#End Region

#Region " Visio Properties "



    '
    '
    '
    Public Sub UpdateAttrLocationProperties _
        (ByRef visShape As Microsoft.Office.Interop.Visio.Shape, _
         ByVal drAttrLoc As System.Data.DataRow)

        Try
            With m_dc

                If Not (drAttrLoc Is Nothing) Then

                    moveDataRowToProperties(drAttrLoc)

                Else

                    Exit Sub

                End If


                _clsVisio.ForceShapeData(visShape, "attrlocName", .attrlocName, "attrLocation")
                _clsVisio.ForceShapeData(visShape, "attrlocKey", CStr(.attrlocKey), "attrLocation")
                _clsVisio.ForceShapeData(visShape, "attrlocSiteKey", CStr(.attrlocSiteKey), "attrLocation")
                _clsVisio.ForceShapeData(visShape, "attrlocBuilding", .attrlocBuilding, "attrLocation")
                _clsVisio.ForceShapeData(visShape, "attrlocFloor", .attrlocFloor, "attrLocation")
                _clsVisio.ForceShapeData(visShape, "attrlocRoom", .attrlocRoom, "attrLocation")
                _clsVisio.ForceShapeData(visShape, "attrlocPhone", .attrlocPhone, "attrLocation")


            End With



        Catch com As COMException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(com, "Com+ Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub





    ' definitions for the containers
    Private Const constSublocPinX As String = "1.5 in"
    Private Const constSublocPinY As String = "9.875 in"


    '
    ' based on the attrloc key, read the sublocation record from the data source
    '
    Public Function buildAttrLocationContainer _
                        (ByVal intKey As Int32, _
                        ByVal _clsData As clsDC_RackData) _
                        As Microsoft.Office.Interop.Visio.Shape

        Dim shpContainer As Microsoft.Office.Interop.Visio.Shape = Nothing

        Try

            drawingDocument = drawingPage.Document
            _clsVisio.drawingPage = drawingPage


            Dim colInput As Collection = New Collection
            Dim locationQuery = From sublocation In _clsData.m_ds.Tables(DC_RackTables.tblAttrLocation.ToString).AsEnumerable() _
                       Where sublocation!attrlocKey = intKey
                      Select sublocation

            Dim drTemp As System.Data.DataRow = Nothing
            Dim strTemp As String = ""
            ' since both ends of a link may be at the same location test to make sure we only keep one of them

            For Each l In locationQuery
                strTemp = "attrlocKey_" & l.Item("attrlocKey").ToString
                If colInput.Contains(strTemp) = False Then
                    colInput.Add(strTemp, strTemp)
                    drTemp = l
                    Exit For
                End If
            Next l

            ' create a list control
            Dim listControl As Microsoft.Office.Interop.Visio.Shape = Nothing
            ' pass in the created collection and return a populated list control
            Dim colMembers As Collection = New Collection


            ' get the datagraphic
            Dim strStencil As String = ""
            Dim strMaster As String = ""
            Dim strDGMaster As String = ""
            Dim visDG As Microsoft.Office.Interop.Visio.Master = Nothing
            Dim intLabelFont As Integer
            Dim intValueFont As Integer
            Dim dblCalloutWidth As Double
            Dim dgmasterQuery = From datagraphic In _clsData.m_ds.Tables(DC_RackTables.tblDGMasters.ToString).AsEnumerable() _
                                Where datagraphic!dgmTable = DC_RackTables.tblAttrLocation.ToString _
                                Select datagraphic

            ' should only be one..
            For Each m In dgmasterQuery
                If LCase(m!dgmTable) = LCase(DC_RackTables.tblAttrLocation.ToString) Then
                    strStencil = m!dgmSurrogateStencil
                    strStencil = _CustomStencilPath & strStencil
                    strMaster = m!dgmSurrogateMaster
                    intLabelFont = CInt(m!dgmFontLabel)
                    intValueFont = CInt(m!dgmFontValue)
                    dblCalloutWidth = CDbl(m!dgmCalloutWidth)
                    strDGMaster = m!dgmMaster
                    Exit For
                End If
            Next

            ' pass in the created collection and return a populated list control
            colMembers = _clsVisio.dropDropDownBoxItems(colInput, strMaster, strStencil, "1.5 in", "1 in", vbTrue)


            Dim visShape As Microsoft.Office.Interop.Visio.Shape = Nothing
            Dim visCell As Microsoft.Office.Interop.Visio.Cell = Nothing
            Dim strName As String = ""
            Dim strKey As String = ""
            ' should only be one
            For Each shapeid In colMembers

                visShape = drawingPage.Shapes.ItemFromID(shapeid)

                If visShape.CellExists("user.attrkey", False) = True Then
                    strName = visShape.Cells("user.attrkey").ResultStr("")
                Else
                    strName = ""
                    GoTo NextShape
                End If

                ' clear any shape data properties
                _clsVisio.deleteShapeData(visShape)

                ' set it semi-transparent
                Dim strTrans As String = "10%"
                ' first the fill properties
                _clsVisio.recurseApplyCellFormula(visShape, VisSectionIndices.visSectionObject, VisRowIndices.visRowFill, VisCellIndices.visFillForegndTrans, strTrans)
                _clsVisio.recurseApplyCellFormula(visShape, VisSectionIndices.visSectionObject, VisRowIndices.visRowFill, VisCellIndices.visFillBkgndTrans, strTrans)
                ' then the line properties
                _clsVisio.recurseApplyCellFormula(visShape, VisSectionIndices.visSectionObject, VisRowIndices.visRowLine, VisCellIndices.visLineColorTrans, strTrans)
                ' prep it for applying the datagraphics by applying the data from the dgmasters table
                _clsVisio.addDataGraphicUserFieldsToShape _
                        (visShape, _
                        intValueFont.ToString, _
                        intLabelFont.ToString, _
                        dblCalloutWidth.ToString)

                'Check that the requested Data Graphic Master exits
                For Each visMaster In drawingDocument.Masters
                    If visMaster.Type = VisMasterTypes.visTypeDataGraphic Then
                        If visMaster.Name = strDGMaster Then
                            visDG = visMaster
                            Exit For
                        End If
                    End If
                Next

                UpdateAttrLocationProperties(visShape, drTemp)

                visShape.Text = drTemp.Field(Of String)("attrlocName")

                ' move the component name to comment field so we get mouse over
                If visShape.CellsSRCExists(VisSectionIndices.visSectionObject, VisRowIndices.visRowMisc, VisCellIndices.visComment, False) = True Then
                    visCell = visShape.CellsSRC(VisSectionIndices.visSectionObject, VisRowIndices.visRowMisc, VisCellIndices.visComment)
                    _clsVisio.SetCellValueToString(visCell, strName)
                End If


                ' allow the legend to keep track of the shapes
                _clsVisio.ForceUserProperty(visShape, "visLegendShape", "2.0")


                'Apply the Data Graphic to the Shape
                visShape.DataGraphic = visDG

                ' unlock the groups so we can change the font sizes
                _clsVisio.recurseApplyCellFormula(visShape, VisSectionIndices.visSectionObject, VisRowIndices.visRowLock, VisCellIndices.visLockGroup, "0")

                If visShape.CellExists("user.dbIntLabelFont", False) = True Then

                    ' get the loaded values 
                    intLabelFont = CInt(visShape.Cells("user.dbintlabelfont").ResultIU)
                    intValueFont = CInt(visShape.Cells("user.dbintvaluefont").ResultIU)
                    dblCalloutWidth = CDbl(visShape.Cells("user.dbdblcallout").ResultIU)

                    ' set the callout width = 85% of the surrogate shapes width (as a formula) so that it fits inside the shape
                    Dim strId As String = visShape.NameID & "!Width*.85"
                    strId = "Guard(" & strId & ")"

                    _clsVisio.recurseApplyCustomFormula(visShape, "prop.msvcalloutproplabelsize", intLabelFont.ToString & " pt")
                    _clsVisio.recurseApplyCustomFormula(visShape, "prop.msvcalloutpropvaluesize", intValueFont.ToString & " pt")
                    _clsVisio.recurseApplyCustomFormula(visShape, "prop.msvcalloutpropwidth", strId)

                End If
NextShape:
            Next shapeid

            shpContainer = _clsVisio.addContainerToPage("Container 4", "", "")
            'shpContainer.Name = "contComponent"
            'shpContainer.NameU = "contComponent"

            listControl = _clsVisio.loadListControl(colMembers, VisListAlignment.visListAlignLeftOrTop, VisListDirection.visListDirTopToBottom)
            'listControl.Name = "listComp"
            'listControl.NameU = "listComp"

            ' add the list control to the container
            _clsVisio.AddShapeToContainer(shpContainer, listControl)

            shpContainer.Cells("pinx").Formula = constSublocPinX
            shpContainer.Cells("piny").Formula = constSublocPinY



        Catch com As COMException
            '********* visio snippet vb.net vis0056vb ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(com, "Com+ Policy")
            If (rethrow) Then
                Throw
            End If
        Catch sql As SqlException
            '********* visio snippet vb.net vis0057vb ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            '********* visio snippet vb.net vis0059vb ******************
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If

        End Try

        Return shpContainer

    End Function




#End Region


#Region " Constructor "

    '
    '
    '
    Public Sub New(ByVal visApp As Microsoft.Office.Interop.Visio.Application, _
                   ByVal visDoc As Microsoft.Office.Interop.Visio.Document)

        Try


            m_db = DatabaseFactory.CreateDatabase()
            strUserId = Environment.UserDomainName & "/" & Environment.UserName

            '
            ' init an object class so that the default constants are available
            ' to the table definitions
            '

            If m_dc IsNot Nothing Then
                m_dc = Nothing
                m_dc = New dc_SubLocation(strUserId)
            Else
                m_dc = New dc_SubLocation(strUserId)
            End If

            initDataSetAndTable()


            drawingApplication = visApp
            drawingDocument = visDoc

            _clsVisio = New visRackLocations.clsVisioSupport
            _clsVisio.drawingApplication = visApp
            _clsVisio.drawingDocument = visDoc



            m_updateCommand = Nothing
            m_insertCommand = Nothing
            m_deleteCommand = Nothing

            buildInsertAttrLocationCommand()
            buildDeleteAttrLocationCommand()
            buildUpdateAttrLocationCommand()


        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


#End Region




End Class

